﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.DAL;
using SWDataDB.Models;
using System.Data.SqlClient;

namespace SWDataDB.DAL
{
    /// <summary>
    ///  相关数据(2013-05-19：zhhw 最终修改完成)
    /// </summary>

    public class XGInfo
    {
        public XGInfo() { }

        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回dataSet</returns>
        public DataSet GetAllInfoBySet()
        {
            string strSql = "SELECT * FROM dbo.XGDATA_TBL";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回datatable</returns>
        public DataTable GetAllInfoByTable()
        {
            string strSql = "SELECT * FROM dbo.XGDATA_TBL";
            return SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到记录for 分页显示，升序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByASC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.XGDATA_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID ASC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到著录表记录for 分页显示，降序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByDESC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.XGDATA_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID DESC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 分页查找
        /// </summary>
        /// <param name="page">页数(第几页)</param>
        /// <param name="num">每页数目</param>
        /// <returns></returns>
        public IList<XGDATA_TBL> GetInfoByPage(long page, long num, string strWhere)
        {
            IList<XGDATA_TBL> xgLst = new List<XGDATA_TBL>();
            string strSql = string.Empty;
            strSql = SQLHelper.GetPagingSQL("XGDATA_TBL", "FILE_ID", "*", strWhere, "FILE_ID DESC", page, num);

            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            try
            {
                foreach (DataRow dr in dt.Rows)
                {
                    XGDATA_TBL xg_info = new XGDATA_TBL();
                    xg_info.O_ID = int.Parse(dr["ID"].ToString());
                    xg_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    xg_info.O_XG_CODE = dr["XG_CODE"].ToString();
                    xg_info.O_DATA_STYLE = dr["DATA_STYLE"].ToString();
                    xg_info.O_DATA_NAME = dr["DATA_NAME"].ToString();
                    xg_info.O_SCALE = dr["SCALE"].ToString();
                    xg_info.O_NUM = int.Parse(dr["NUM"].ToString());
                    xgLst.Add(xg_info);
                }

                return xgLst;
            }
            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 通过file_ID 查询记录
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public XGDATA_TBL SelectXGInfoById(string xg_code)
        {
            XGDATA_TBL xg_info = new XGDATA_TBL();
            string strSql = "SELECT * FROM dbo.XGDATA_TBL WHERE XG_CODE='";
            strSql += xg_code + "'";

            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            if (dt.Rows.Count < 1)
                return null;

            try
            {
                foreach (DataRow dr in dt.Rows)
                {
                    xg_info.O_ID = int.Parse(dr["ID"].ToString());
                    xg_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    xg_info.O_DATA_STYLE = dr["DATA_STYLE"].ToString();
                    xg_info.O_DATA_NAME = dr["DATA_NAME"].ToString();
                    xg_info.O_SCALE = dr["SCALE"].ToString();
                    xg_info.O_NUM = int.Parse(dr["NUM"].ToString());
                }

                return xg_info;
            }
            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        public DataSet SelectInfoByFileID(string zl_file_id)
        {
            string strSql = "SELECT * FROM dbo.XGDATA_TBL WHERE ZL_FILE_ID ='";
            strSql += zl_file_id + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }




        public DataSet SelectInfoByFileID(int xgid)
        {
            string strSql = "SELECT * FROM dbo.XGDATA_TBL WHERE ID =";
            strSql += xgid;

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="xg_info"></param>
        /// <returns></returns>
        public int AddXGInfo(XGDATA_TBL xg_info)
        {
            string strSql = "INSERT INTO dbo.XGDATA_TBL(XG_CODE,ZL_FILE_ID,DATA_STYLE,DATA_NAME,SCALE,NUM) VALUES(";
            strSql += "'" + xg_info.O_XG_CODE + "',";
            strSql += "'" + xg_info.O_ZL_FILE_ID + "',";
            strSql += "'" + xg_info.O_DATA_STYLE + "',";
            strSql += "'" + xg_info.O_DATA_NAME + "',";
            strSql += "'" + xg_info.O_SCALE + "',";
            strSql += xg_info.O_NUM + ")";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 删除记录 通过file_ID
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public int DeleteXGInfoById(int id)
        {
            string strSql = "DELETE FROM dbo.XGDATA_TBL WHERE id = ";
            strSql += id.ToString();

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }




        public DataSet SelectByXGID(string xgid)
        {
            string strSql = "SELECT * FROM dbo.XGDATA_TBL WHERE ID ='";
            strSql += xgid + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }












        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="xg_info"></param>
        /// <returns></returns>
        public int UpdateXGInfo(XGDATA_TBL xg_info)
        {
            string strSql = "UPDATE dbo.XGDATA_TBL ";
            strSql += "SET XG_CODE='" + xg_info.O_XG_CODE + "',";
            strSql += " ZL_FILE_ID='" + xg_info.O_ZL_FILE_ID + "',";
            strSql += " DATA_STYLE='" + xg_info.O_DATA_STYLE + "',";
            strSql += " DATA_NAME='" + xg_info.O_DATA_NAME + "',";
            strSql += " SCALE='" + xg_info.O_SCALE + "',";
            strSql += " NUM='" + xg_info.O_NUM + "'";
            strSql += "WHERE ID=" + xg_info.O_ID;

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        #region =影像数据操作=
        public int AppendImage(int id, byte[] bImg, long len)
        {
            string SQL = "UPDATE  dbo.XGDATA_TBL SET IMAGE = @IMAGE WHERE ID=" + id.ToString();

            SqlParameter[] commandParameters = new SqlParameter[1];
            commandParameters[0] = new SqlParameter("@IMAGE", SqlDbType.Image);
            commandParameters[0].Value = bImg;

            try
            {
                SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, SQL, commandParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }


            return 1;
        }

        public byte[] GetImage(int id)
        {
            byte[] photo = null;
            string sql = "SELECT IMAGE FROM dbo.XGDATA_TBL WHERE ID =" + id.ToString();
            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.SQLConnString, CommandType.Text, sql, null);//采用SqlDataReader的方法来读取数据

            if (reader.Read())
                photo = reader[0] as byte[];//将第0列的数据写入byte数组 

            reader.Close();                 //关闭reader
            return photo;
        }

        #endregion
    }
}
